package mysql;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.TypeReference;
import com.alibaba.fastjson.serializer.SerializerFeature;
import configuration.RobConfiguration;
import enumeration.EnumRobStrategy;
import enumeration.EnumUserType;
import exception.LoginErrorException;
import http.BasicRequest;
import http.util.HtmlUtil;
import http.util.ParseUtil;
import http.util.PrintUtil;
import main.robClass;
import okhttp3.*;
import org.apache.commons.lang3.StringUtils;
import robber.AbstractRobber;
import robber.IRobber;
import robber.impl.CommonRobber;
import robber.impl.PERobber;
import user.*;
import user.IUser;
import user.impl.BetterUser;
import user.impl.OOUser;
import user.impl.SimpleUserDTO;
import user.impl.robUser.IRobUser;
import user.impl.robUser.RobUser;

import javax.sql.rowset.serial.SerialClob;
import java.io.*;
import java.sql.*;
import java.sql.Connection;
import java.util.*;

import static java.lang.Thread.sleep;
import static main.robClass.register;

public class MysqlUtil {

    // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/robClass?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false";

    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    //static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    //static final String DB_URL = "jdbc:mysql://localhost:3306/RUNOOB?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";

    final static String username = "root";
    final static String password = "root";
    private static Connection connection;
    private static Statement statement;

    public static void initMysql() {
        try {
            Class.forName(JDBC_DRIVER);
            robClass.logger.info("connect to mysql...");
            connection = DriverManager.getConnection(DB_URL, username, password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static ResultSet select(String sql) {
        if (connection == null) {
            initMysql();
            return select(sql);
        }
        try {
//            robClass.logger.info(sql);
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            return resultSet;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * username int(50) #每秒
     * password int(50)
     * startTime Date #开始时间
     * thread int(5) 每秒线程
     * strategy int 抢课策略  ->enum  0 List 1 Random 2 All
     * state int  抢课是否成功 0 未成功 1 成功
     */
    public static ResultSet selectAllUsers() {
        String selectSql = "select * from user";
        ResultSet resultSet = select(selectSql);
        return resultSet;
    }

    /**
     * 选择学生
     */
    public static ResultSet selectUser(IUser user) {
        String selectSql = "select * from user where username= " + user.getUsername();
        ResultSet resultSet = select(selectSql);
        try {
            if (resultSet.next()) {
                return resultSet;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    public static void autoSetStudentName_better(IUser user) {
        RobUser robUser = new BetterUser();
        robUser.setUsername(user.getUsername());
        robUser.setPassword(user.getPassword());
        try {
            robUser.login();
            Request request = robUser.getBasicRequest().getBasicRequest().newBuilder()
                    .url((RobConfiguration.URL + "/xs_main.aspx?xh=" + user.getUsername()))
                    .addHeader("cookie", user.getRandomCookie())
                    .get()
                    .build();
            Call call2 = robUser.getBasicRequest().getDefaultHttpClient().newCall(request);
            call2.enqueue(new Callback() {
                @Override
                public void onFailure(Call call, IOException e) {
                    robClass.logger.info("getStudentName - 网络异常");
                }
                @Override
                public void onResponse(Call call, Response response) throws IOException {
                    String responseHtml = response.body().string();
                    response.close();
                    String studentName = ParseUtil.parseStudentNameSpan(responseHtml);
                    if (studentName == null) {
                        robClass.logger.info("getStudentName - 获取不到学生名字 请手动录入!");
                    }
                    user.setStudentName(studentName);
                    robClass.logger.info("自动识别用户姓名:" + user.getStudentName());
                    updateUser(user);
                }
            });
        } catch (Exception e) {
            robClass.logger.info("getStudentName - 网络异常");
        }


    }

    /**
     * 自动设置学生名字
     */
    public static void autoSetStudentName(IUser user) {
        if (StringUtils.isBlank(user.getStudentName()) || user.getStudentName().equalsIgnoreCase("0")) {
            PERobber peRobber = new PERobber(user);
//            + "&" + user.getUid();
            String uri = "/default4.aspx?xh=" + user.getUsername();
            String bodyContent = "__VIEWSTATE=dDwxMTE4MjQwNDc1Ozs%2B%2BOcVL6V79a6y5AS0L93bcGUk638%3D&TextBox1=" + user.getUsername() + "&TextBox2=" + user.getPassword() + "&RadioButtonList1=%D1%A7%C9%FA&Button1=+%B5%C7+%C2%BC+";
            Request request = peRobber.getBasicRequest().getBasicRequest().newBuilder()
                    .url(RobConfiguration.URL + uri)
                    .post(RequestBody.create(MediaType.parse(BasicRequest.defaultHeader), bodyContent))
                    .build();
            Call call = peRobber.getBasicRequest().getDefaultHttpClient().newCall(request);
            call.enqueue(new Callback() {
                @Override
                public void onFailure(Call call, IOException e) {
                    robClass.logger.info("异步 *获取用户姓名* 登录失败: 网络请求超时");
                }

                @Override
                public void onResponse(Call call, Response response) throws IOException {
                    Headers headers = response.headers();
                    response.close();
                    HttpUrl url = request.url();
                    List<Cookie> cookies = Cookie.parseAll(url, headers);
                    if (cookies != null) {
                        String cookie = HtmlUtil.okHttp3CookieToString(cookies);
                        peRobber.getBasicRequest().updateCookies(user, cookie);
                        try {
                            Request request = peRobber.getBasicRequest().getBasicRequest().newBuilder()
                                    .url((RobConfiguration.URL + "/xs_main.aspx?xh=" + peRobber.getUser().getUsername()))
                                    .addHeader("cookie", (String) peRobber.getUser().getCookies().toArray()[0])
                                    .get()
                                    .build();
                            Call call2 = peRobber.getBasicRequest().getDefaultHttpClient().newCall(request);
                            call2.enqueue(new Callback() {
                                @Override
                                public void onFailure(Call call, IOException e) {
                                    robClass.logger.info("getStudentName - 网络异常");
                                }

                                @Override
                                public void onResponse(Call call, Response response) throws IOException {
                                    String responseHtml = response.body().string();
                                    response.close();
                                    String studentName = ParseUtil.parseStudentNameSpan(responseHtml);
                                    if (studentName == null) {
                                        robClass.logger.info("getStudentName - 获取不到学生名字 请手动录入!");

//                                        return;
                                    }
                                    user.setStudentName(studentName);
                                    robClass.logger.info("自动识别用户姓名:" + user.getStudentName());
                                    updateUser(user);
                                }
                            });
                        } catch (Exception e) {
                            robClass.logger.info("getStudentName - 网络异常");
                        }
                    }

                }
            });
        }
    }

    /**
     * 插入更新学生数据
     */
    public static ResultSet updateUser(IUser user) {
        try {
            if (connection == null) {
                initMysql();
            }
            /**用来自动获取学生姓名*/
            if (StringUtils.isBlank(user.getStudentName()) || user.getStudentName().equalsIgnoreCase("0")) {
                user.login();
                sleep(1000);
//                autoSetStudentName(user);
                autoSetStudentName_better(user);
            } else { /**用户有姓名*/
                //先查询是否存在 再插入
                //如果存在就更新
                String insertSql = "insert into user(username,password,studentName,cookies) values(?,?,?,?)";

                String updateSql = "update user SET username= ?, password=?,studentName=?,cookies=? where username = " + user.getUsername();
                PreparedStatement preparedStatement;
                try {
                    if (selectUser(user) != null) { //update state
                        preparedStatement = connection.prepareStatement(updateSql);
                    } else {
                        preparedStatement = connection.prepareStatement(insertSql);
                    }
                    preparedStatement.setString(1, user.getUsername());
                    preparedStatement.setString(2, user.getPassword());
                    preparedStatement.setString(3, user.getStudentName());
                    preparedStatement.setString(4, JSON.toJSONString(user.getCookies(), SerializerFeature.WriteClassName));

                    int rs = preparedStatement.executeUpdate();
//            robClass.logger.info("updateUser : 改动了" + rs + "行");

                } catch (SQLException e) {
                    e.printStackTrace();
                }
                if (user instanceof IRobUser) {
                    IRobUser iRobUser = (IRobUser) user;
                    updateRobUser(iRobUser);
                }
            }
        } catch (Exception e) {
            PrintUtil.printError(user, Thread.currentThread());
        }
        return null;
    }


    /**
     * 更新RobUser信息
     */
    private static ResultSet updateRobUser(IRobUser iRobUser) {
        if (connection == null) {
            initMysql();
        }
        //先查询是否存在 再插入
        //如果存在就更新
        String insertSql = "insert into user(startTime,thread,strategy,robEnable,robClasses) values(?,?,?,?,?)";
        String updateSql = "update user SET startTime=?, thread=?, strategy=?, robEnable=?,robClasses=? where username = " + iRobUser.getUsername();
        PreparedStatement preparedStatement;
        try {
            preparedStatement = selectUser(iRobUser) != null
                    ? connection.prepareStatement(updateSql)
                    : connection.prepareStatement(insertSql);
            preparedStatement.setString(1, iRobUser.getStartTime());
            preparedStatement.setInt(2, iRobUser.getThreads());
            preparedStatement.setInt(3, iRobUser.getEnumRobStrategy().ordinal());
            preparedStatement.setInt(4, iRobUser.getRobEnable());
            preparedStatement.setString(5, JSON.toJSONString(iRobUser.getRobClasses(), SerializerFeature.WriteClassName));
            int rs = preparedStatement.executeUpdate();
//            robClass.logger.info("改动了" + rs + "行");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;

    }

    /**
     * 获取用户
     * 1.缓存获取
     * 2.数据库获取
     */
    public static RobUser getUser(IUser user) {
        RobUser new_user = null;
        if (RobConfiguration.ROB_LOGIN_MODE.equals(EnumUserType.BETTER_USER)) {
            new_user = new BetterUser();
        }
        if (RobConfiguration.ROB_LOGIN_MODE.equals(EnumUserType.OO_USER)) {
            new_user = new OOUser();
        }
        try {
            if (user == null) {
                //查询缓存区是否有账号
                PrintUtil.printTitle("登陆操作");
                robClass.logger.info("正方账号:");
                String input = null;
                /**从缓存中获取用户信息*/
                Scanner strInput = new Scanner(System.in, "UTF-8");
                if (strInput.hasNext()) {
                    input = strInput.next();
                }
//                System.out.println("UserManager.getUserCache()");
//                System.out.println(UserManager.getUserCache());
//                System.out.println("RobConfiguration.ROB_LOGIN_MODE.name()");
//                System.out.println(RobConfiguration.ROB_LOGIN_MODE.name());
                new_user = (RobUser) UserManager.getUserCache().get(RobConfiguration.ROB_LOGIN_MODE.name() + "_" + input);
                if (new_user != null) {
                    return new_user;
                }
                IUser iUser = new SimpleUserDTO();
                iUser.setUsername(input);
                /**如果缓存不存在 将账号加入进缓存*/
                ResultSet rs = MysqlUtil.selectUser(iUser);
                if (rs != null) {
                    robClass.logger.info("数据库中获取RobUser账号数据");
                    System.out.println(rs);
                    if (RobConfiguration.ROB_LOGIN_MODE.equals(EnumUserType.BETTER_USER)) {
                        user = new BetterUser();
                    }
                    if (RobConfiguration.ROB_LOGIN_MODE.equals(EnumUserType.OO_USER)) {
                        user = new OOUser();
                    }
                    new_user = (RobUser) initUser(rs, (RobUser) user);
                } else {
                    /**缓存不存在 账号也不在数据库 新用户判断*/
                    robClass.logger.info("获取不到用户信息! 目前判断你是新用户! 请去填写个人资料!");
                    new_user = register(input);
                }
                /**用户缓存*/
                UserManager.addUserCache(new_user);
                UserManager.addAllUserCache(new_user);
                robClass.logger.info("获取用户信息成功!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new_user;
    }

    /**
     * 从数据库中读取所有用户信息 缓存到UserCache
     */
    public static ArrayList<IUser> getAllUser(EnumUserType mode) {
        ArrayList<IUser> users = new ArrayList<>();

        ResultSet rs = MysqlUtil.selectAllUsers();
//        robClass.logger.info(rs.toString());
        try {
            while (rs.next()) {
                IUser iuser = null;
                /**根据当前执行的模式进行用户信息初始化*/
                if (mode.equals(EnumUserType.BETTER_USER)) {
                    iuser = initUser(rs, new BetterUser());
                }
                if (mode.equals(EnumUserType.OO_USER)) {
                    iuser = initUser(rs, new OOUser());
                }

                if (((IRobUser) iuser).getRobEnable() == 1) {
                    if (StringUtils.isBlank(iuser.getStudentName())) {
                        System.out.println(iuser);
                        System.out.println(iuser.getPassword());
                        System.out.println("要抢课但空名字");
                        iuser.login();
                        sleep(2000);
                        autoSetStudentName_better(iuser);
                    }
                    users.add(iuser);
                    UserManager.addUserCache(iuser);
                    UserManager.addAllUserCache(iuser);
                }
            }
        } catch (SQLException | LoginErrorException | InterruptedException e) {
            e.printStackTrace();
        }
//        robClass.logger.info(users);
        return users;
    }


    /**数据库获取的user信息 与User类orm*/
    /**
     * 设置课程 Cookie
     */
    public static IUser initUser(ResultSet rs, RobUser user) {
        /**决定登录方式*/
        try {
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setStudentName(rs.getString("studentName"));
            user.setRobEnable(rs.getInt("robEnable"));
            user.setStartTime(rs.getString("startTime"));
            user.setThreads(rs.getInt("thread"));
            user.setEnumRobStrategy(EnumRobStrategy.values()[rs.getInt("strategy")]);
            ArrayList<String> robClasses = (ArrayList<String>) JSON.parseObject(rs.getString("robClasses"), ArrayList.class);
            if (robClasses == null) {
                robClasses = new ArrayList<>();
            }
            ArrayList<String> cookies = (ArrayList<String>) JSON.parseObject(rs.getString("cookies"), ArrayList.class);
            if (cookies == null) {
                cookies = new ArrayList<>();
            }
            user.setRobClasses(robClasses);
            user.setCookies(cookies);
            //robClass.logger.info("initUser:" + user);
        } catch (SQLException e) {
            System.out.println(rs);
            e.printStackTrace();
        }
        return user;
    }

    /*
     * 根据抢课类型初始化抢课数据
     */
    public static AbstractRobber initAbstractRobber(ResultSet rs, AbstractRobber abstractRobber) {
        if (abstractRobber instanceof PERobber) {
            abstractRobber = initPERobber(rs, abstractRobber);
            if (abstractRobber == null) {
                return null;
            }
        }
        if (abstractRobber instanceof CommonRobber) {
            abstractRobber = initCommonRobber(rs, abstractRobber);
            if (abstractRobber == null) {
                return null;
            }
        }
        return abstractRobber;
    }

    /** 加载体育课发包三大参数*/
    /**
     * 加载搜索课程后的第一个viewState
     */
    public static String ClobToString(Clob clob) throws SQLException, IOException {
        String reString = "";
        if (clob == null) return reString;
        Reader is = clob.getCharacterStream();
        BufferedReader br = new BufferedReader(is);
        String s = br.readLine();
        StringBuffer sb = new StringBuffer();
        while (s != null) {
            sb.append(s);
            s = br.readLine();
        }
        reString = sb.toString();
        if (br != null) {
            br.close();
        }
        if (is != null) {
            is.close();
        }
        return reString;
    }
    /**从数据库中拉取持久化的initCommonRobber数据 并将其载入java虚拟机内存*/
    /**
     * commonRobber只需要firstViewState
     */
    public static HashMap syncHashMap(HashMap map, List list) {
        System.out.println("list数量:" + list.size());
        System.out.println("map数量:" + map.size());
        HashMap newHashMap = new HashMap();
        list.stream().forEach(a_element -> {
            newHashMap.put(a_element, map.get(a_element));
        });
        System.out.println("过滤后newHashMap数量:" + newHashMap.size());
        return newHashMap;
    }

    public static AbstractRobber initCommonRobber(ResultSet rs, AbstractRobber abstractRobber) {
        try {
            CommonRobber commonRobber = (CommonRobber) abstractRobber;
            IUser user = commonRobber.getUser();
            ArrayList<String> cookies = user.getCookies();
            Clob cookieAndFirstViewStateClob = rs.getClob("firstViewState");
            Clob cookieAndPostViewStateClob = rs.getClob("commonCourseViewState");
            HashMap<String, String> cookieAndFirstViewState = (HashMap<String, String>) JSON.parseObject(ClobToString(cookieAndFirstViewStateClob), new TypeReference<Map<String, String>>() {
            });
            HashMap<String, String> cookieAndPostViewState = (HashMap<String, String>) JSON.parseObject(ClobToString(cookieAndPostViewStateClob), new TypeReference<Map<String, String>>() {
            });

            if (cookieAndFirstViewState == null) {
                cookieAndFirstViewState = new HashMap<String, String>();
            }
            if (cookieAndPostViewState == null) {
                cookieAndPostViewState = new HashMap<String, String>();
            }
            PrintUtil.printTitle("执行 - 过滤无用Cookie");
            cookieAndFirstViewState = syncHashMap(cookieAndFirstViewState, cookies);
            cookieAndPostViewState = syncHashMap(cookieAndPostViewState, cookies);
            PrintUtil.printTitle("完毕 - 过滤无用Cookie");
            commonRobber.setCookieAndFirstViewState(cookieAndFirstViewState);
            commonRobber.setCookieAndPostViewState(cookieAndPostViewState);
            robClass.logger.info("initCommonRobber:" + commonRobber);
            PrintUtil.printTitle("检测到可用缓存 - 装载公选课发包必备参数中");
            return commonRobber;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从数据库中拉取持久化的initPERobber数据 并将其载入java虚拟机内存
     */
    public static AbstractRobber initPERobber(ResultSet rs, AbstractRobber abstractRobber) {
        try {
            PERobber peRobber = (PERobber) abstractRobber;
            Clob clob = rs.getClob("must3Params");
            Clob clob2 = rs.getClob("firstViewState");
//            HashMap<String, ArrayList<ArrayList<String>>> idToViewStateAndListBoxAndCookieCache = (HashMap<String, ArrayList<ArrayList<String>>>) JSON.parseObject(rs.getString("must3Params"), HashMap.class);
//            HashMap<String, ArrayList<ArrayList<String>>> idToViewStateAndListBoxAndCookieCache = (HashMap<String, ArrayList<ArrayList<String>>>) JSON.parseObject(ClobToString(clob), HashMap.class);
            HashMap<String, ArrayList<ArrayList<String>>> idToViewStateAndListBoxAndCookieCache;
//            HashMap<String, ArrayList<ArrayList<String>>> idToViewStateAndListBoxAndCookieCache = (HashMap<String, ArrayList<ArrayList<String>>>) JSON.parseObject(ClobToString(clob), new TypeReference<Map<String, ArrayList<ArrayList<String>>>>() {
            try {
                idToViewStateAndListBoxAndCookieCache = (HashMap<String, ArrayList<ArrayList<String>>>) JSON.parseObject(ClobToString(clob), new TypeReference<Map<String, ArrayList<ArrayList<String>>>>() {
                });
            } catch (JSONException e) {
                robClass.logger.info(abstractRobber.getUser().getUsername() + " #must3Params初始化异常");
                idToViewStateAndListBoxAndCookieCache = new HashMap<String, ArrayList<ArrayList<String>>>();
            }
//            JSONObject.parseObject(JSONObject.toJSONString(map.get("list")),ArrayList.class);
            HashSet<String> firstViewState = (HashSet<String>) JSON.parseObject(ClobToString(clob2), HashSet.class);
//            HashSet<String> firstViewState = (HashSet<String>) JSON.parseObject(rs.getString("firstViewState"), HashSet.class);
            if (idToViewStateAndListBoxAndCookieCache == null) {
                robClass.logger.info("idToViewStateAndListBoxAndCookieCache 初始化");
                idToViewStateAndListBoxAndCookieCache = new HashMap<String, ArrayList<ArrayList<String>>>();
            }
//            robClass.logger.info("initPERobber - idToViewStateAndListBoxAndCookieCache" + idToViewStateAndListBoxAndCookieCache);
            if (firstViewState == null) {
                firstViewState = new HashSet<>();
            }
            peRobber.setIdToViewStateAndListBoxAndCookieCache(idToViewStateAndListBoxAndCookieCache);
            peRobber.setFirstViewState(firstViewState);
//            robClass.logger.info("initPeRobberr:" + peRobber);
//            PrintUtil.printTitle("检测到缓存 装载体育课发包必备参数中");
            return peRobber;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Clob getClob(String s) {
        try {
            return new SerialClob(s.toCharArray());
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 获取抢课需要的数据
     */
    public static AbstractRobber loadAbstractRobberData(AbstractRobber abstractRobber, String username) {
        String selectSql = "select * from robber where username= " + username;
        ResultSet rs = select(selectSql);
        try {
            if (rs.next()) {
                return initAbstractRobber(rs, abstractRobber);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 设置抢课课程列表后更新抢课者内容
     */
    synchronized
    public static ResultSet updatePERobber(IRobber iRobber) {
        PERobber peRobber = (PERobber) iRobber;
        IUser user = peRobber.getUser();

        if (connection == null) {
            initMysql();
        }

        String insertSql = "insert into robber(username,must3Params,firstViewState) values(?,?,?)";
        String updateSql = "update robber SET username=?, must3Params=?, firstViewState=? where username = " + user.getUsername();
        PreparedStatement preparedStatement;

        try {

            preparedStatement = selectRobber(iRobber) != null
                    ? connection.prepareStatement(updateSql)
                    : connection.prepareStatement(insertSql);
            Clob clob = getClob(JSON.toJSONString(peRobber.getIdToViewStateAndListBoxAndCookieCache(), SerializerFeature.WriteClassName));
            Clob clob2 = getClob(JSON.toJSONString(peRobber.getFirstViewState(), SerializerFeature.WriteClassName));

            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setClob(2, clob);
            preparedStatement.setClob(3, clob2);
            int rs = preparedStatement.executeUpdate();
//            robClass.logger.info("updatePERobber - 改动了" + rs + "行");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    synchronized
    public static ResultSet updateCommonRobber(IRobber iRobber) {
        CommonRobber commonRobber = (CommonRobber) iRobber;
        IUser user = commonRobber.getUser();
        if (connection == null) {
            initMysql();
        }
        String insertSql = "insert into robber(username,firstViewState,commonCourseViewState) values(?,?,?)";
        //String updateSql = "update robber SET username=?, firstViewState=?, commonCourseViewState=?, where username = " + user.getUsername();
        String updateSql;
        PreparedStatement preparedStatement = null;
        try {
//            if (commonRobber.getFirstViewState() != null && !commonRobber.getFirstViewState().isEmpty()) {
//                updateSql = "update robber SET username=?, firstViewState=?, where username = " + user.getUsername();

//                System.out.println(commonRobber.getFirstViewState());
//                Clob firstViewStateClob = getClob(JSON.toJSONString(commonRobber.getFirstViewState(), SerializerFeature.WriteClassName));
//                preparedStatement.setString(1, user.getUsername());
//                preparedStatement.setClob(2, firstViewStateClob);
//                System.out.println("第一个");
//                int rs = preparedStatement.executeUpdate();
//                robClass.logger.info("updateCommonRobber - 改动了" + rs + "行");
//            }
//            if (commonRobber.getCookieAndPostViewState() != null && !commonRobber.getCookieAndPostViewState().isEmpty()) {
//                updateSql = "update robber SET username=?, commonCourseViewState=?, where username = " + user.getUsername();
//                preparedStatement = selectRobber(iRobber) != null
//                        ? connection.prepareStatement(updateSql)
//                        : connection.prepareStatement(insertSql);
//                System.out.println(commonRobber.getCookieAndPostViewState());
//                Clob commonCourseViewStateClob = getClob(JSON.toJSONString(commonRobber.getCookieAndPostViewState(), SerializerFeature.WriteClassName));
//                preparedStatement.setString(1, user.getUsername());
//                preparedStatement.setClob(2, commonCourseViewStateClob);
//                System.out.println("第二个");
//                int rs = preparedStatement.executeUpdate();
//                robClass.logger.info("updateCommonRobber - 改动了" + rs + "行");
//            }


//            System.out.println("updateCommonRobber");
//            System.out.println(commonRobber.getCookieAndFirstViewState());
//            System.out.println(commonRobber.getCookieAndPostViewState());
//            Clob testClob = getClob(JSON.toJSONString("", SerializerFeature.WriteClassName));
//            preparedStatement =connection.prepareStatement(insertSql);
//            preparedStatement.setString(1, user.getUsername());
//            preparedStatement.setClob(2, testClob);
//            preparedStatement.setClob(3, testClob);
//            int rs2 = preparedStatement.executeUpdate();
//                           robClass.logger.info("updateCommonRobber - 改动了" + rs2 + "行");
            if ((commonRobber.getCookieAndPostViewState() != null && !commonRobber.getCookieAndPostViewState().isEmpty()) && (commonRobber.getCookieAndFirstViewState() != null && !commonRobber.getCookieAndFirstViewState().isEmpty())) {
                updateSql = "update robber SET username=?, firstViewState=?,commonCourseViewState=? where username=" + user.getUsername();
                preparedStatement = selectRobber(iRobber) != null
                        ? connection.prepareStatement(updateSql)
                        : connection.prepareStatement(insertSql);
                //preparedStatement =connection.prepareStatement(insertSql);
                Clob firstViewStateClob = getClob(JSON.toJSONString(commonRobber.getCookieAndFirstViewState(), SerializerFeature.WriteClassName));
                Clob commonCourseViewStateClob = getClob(JSON.toJSONString(commonRobber.getCookieAndPostViewState(), SerializerFeature.WriteClassName));
                preparedStatement.setString(1, user.getUsername());
                preparedStatement.setClob(2, firstViewStateClob);
                preparedStatement.setClob(3, commonCourseViewStateClob);
//                System.out.println("第三个");
                int rs = preparedStatement.executeUpdate();
//                robClass.logger.info("updateCommonRobber - 改动了" + rs + "行");
            }
            if (preparedStatement == null) {
//                robClass.logger.info("updateCommonRobber - 改动失败");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 选择所有Robber
     */
    public static ResultSet selectRobbers() {
        String selectSql = "select * from robber";
        ResultSet resultSet = select(selectSql);
        return resultSet;
    }

    /**
     * 根据用户学号选择UserRobber
     */
    public static ResultSet selectRobber(IRobber iRobber) {
        IUser user = ((AbstractRobber) iRobber).getUser();
        String selectSql = "select * from robber where username= " + user.getUsername();
        ResultSet resultSet = select(selectSql);
        try {
            if (resultSet.next()) {
                return resultSet;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**全局抢课课表备份*/
    /**channelName为专科/本科教务系统*/
    /**courseList 课程列表*/

    /**
     * "04TY47":"篮球"
     */
    public static ResultSet updateCourseTables(String channelName, HashMap<String, String> backupCourseList) {

        if (connection == null) {
            initMysql();
        }

        String insertSql = "insert into courses(channelName,courseList) values(?,?)";
        String updateSql = "update courses SET channelName=?, courseList=? where channelName = " + "'" + channelName + "'";
        PreparedStatement preparedStatement;

        try {
            preparedStatement = selectCourseType(channelName) != null
                    ? connection.prepareStatement(updateSql)
                    : connection.prepareStatement(insertSql);

            preparedStatement.setString(1, channelName);
            preparedStatement.setString(2, JSON.toJSONString(backupCourseList, SerializerFeature.WriteClassName));
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 根据channelName列名选择课表
     */
    public static ResultSet selectCourseType(String channelName) {
        String selectSql = "select * from courses where channelName=" + "'" + channelName + "'";
        robClass.logger.info(selectSql);
        ResultSet resultSet = select(selectSql);
        try {
            if (resultSet.next()) {
                return resultSet;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 加载所有课表
     */
    public static HashMap<String, String> initCourseList(String channelName) {
        try {
            ResultSet rs = selectCourseType(channelName);
            if (rs == null) {
                return new HashMap<String, String>();
            }
            return (HashMap<String, String>) JSON.parseObject(rs.getString("courseList"), new TypeReference<HashMap<String, String>>() {
            });
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}


